package com.uuticketweb.ticket.flight.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.uuticketweb.ticket.base.UUticketException;
import com.uuticketweb.ticket.db.HibernateUtils;
import com.uuticketweb.ticket.db.HqlUtils;
import com.uuticketweb.ticket.pojos.PlaneInfo;

public class FlightDao extends HibernateDaoSupport {
	private static final Logger LOG = Logger.getLogger(FlightDao.class);
	
	/**
	 * 得到航空公司名以及二字码
	 * @return
	 * @throws Exception 
	 */
	public List getTwoCode() throws Exception{
		return getHibernateTemplate().find("from TwoCode twoCode order by twoCode.twocode asc");
	}
	
	public List getAllContinent() throws Exception {
		return getHibernateTemplate().find("select distinct threeCode.continent from ThreeCode threeCode where threeCode.continent is not null");
	}
	
	public List getAllCountry() {
		return getHibernateTemplate().find("select distinct threeCode.countryCode, threeCode.countryChineseName from ThreeCode threeCode order by threeCode.countryCode asc");
	}
	
	public List getCountryOfContinent(String continent) {
		return getHibernateTemplate().find("select distinct threeCode.countryCode, threeCode.countryChineseName from ThreeCode threeCode where threeCode.continent=? order by threeCode.countryCode asc", continent);
	}
	
	public List getAirportOfCountry(String countryCode) {
		return getHibernateTemplate().find("from ThreeCode threeCode where threeCode.countryCode=? order by airportThreeCode asc", countryCode);
	}
	
	public PlaneInfo getPlaneInfo(String planeInfoId) throws UUticketException {
		Long lPlaneInfoId = null;
		try {
			lPlaneInfoId = Long.parseLong(planeInfoId);
		} catch (NumberFormatException e) {
		}
		if(lPlaneInfoId == null) return null;
		
		return (PlaneInfo)HibernateUtils.get(lPlaneInfoId, PlaneInfo.class);
	}
	
	public List getCompanyFromTwocode(String twoCode) {
		return getHibernateTemplate().find("from TwoCode twoCode where twoCode.twocode=?", twoCode);
	}
	
	public List getAirportsCodeInSameCity(String airportThreeCode) {
		return getHibernateTemplate().find("select airportCity.airportThreeCode from AirportCity airportCity where airportCity.cityThreeCode=(select airportCity.cityThreeCode from AirportCity airportCity where airportCity.airportThreeCode=?)", airportThreeCode);
	}
	
	/**
	 * Search flight info with multiple depart airport and dest airport.
	 * @param departAirport
	 * @param destAirport
	 * @param turncityAirport
	 * @param toYear
	 * @param toTransferYear
	 * @param toSeason
	 * @param toTransferSeason
	 * @param toDay
	 * @param toTransferDay
	 * @param backYear
	 * @param backTransferYear
	 * @param backSeason
	 * @param backTransferSeason
	 * @param backDay
	 * @param backTransferDay
	 * @param itineryType
	 * @param company
	 * @return
	 * @throws Exception
	 */
	public List getFlightList(
			String[] departAirport, String[] destAirport, String[] turncityAirport, 
			String toYear, String[] toTransferYear, 
			String toSeason, String[] toTransferSeason, 
			String toDay, String[] toTransferDay, 
			String backYear, String[] backTransferYear, 
			String backSeason, String[] backTransferSeason, 
			String backDay, String[] backTransferDay, 
			int itineryType, String[] company) throws Exception{
		
		Session session = getSession();
		
		StringBuffer cond = new StringBuffer();
		List<String> comConList = new ArrayList<String>();
		if(company != null) {
			for(int i = 0; i < company.length; i ++) {
				comConList.add(" planeInfo.carrier.twocode=:twocode" + i);
			}
		}
		
		if(!comConList.isEmpty()) {
			cond.append(" ( ");
			for(int i = 0; i < comConList.size(); i ++) {
				cond.append(comConList.get(i));
				if(i != comConList.size() - 1) cond.append(" or ");
			}
			cond.append(" ) ");
		}
		
		if(cond.length() > 0) {
			cond.append(" and ");
		}

		if(itineryType == PlaneInfo.TYPE_ONE_WAY) {
			if(turncityAirport == null || turncityAirport.length == 0) {
				// 单程且不中转
				departToDest(cond, departAirport, destAirport);
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
			} else {
				// 单程，有中转
				cond.append(" ( ");
				departToTurncity(cond, departAirport, turncityAirport);
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( "); 
				cond.append(" ( ");
				turncityToDest(cond, turncityAirport, destAirport);
				cond.append(" ) ");
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:toTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:toTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + toTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				cond.append(" ) "); 
			}
		} else {
			if(turncityAirport == null || turncityAirport.length == 0) {
				// 往返，无中转
				cond.append(" ( ");
				departToDest(cond, departAirport, destAirport);
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( ");
				destToDepart(cond, departAirport, destAirport);
				cond.append(" and planeInfo.year=:backYear ");
				cond.append(" and planeInfo.season=:backSeason ");
				cond.append(" and planeInfo.day" + backDay + "='Y' ");
				cond.append(" ) ");
			} else {
				// 往返， 有中转
				cond.append(" ( ");
				departToTurncity(cond, departAirport, turncityAirport);
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( "); 
				turncityToDest(cond, turncityAirport, destAirport);
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:toTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:toTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + toTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				
				cond.append(" ) and (");
				
				turncityToDepart(cond, turncityAirport, departAirport);
				cond.append(" and planeInfo.year=:backYear ");
				cond.append(" and planeInfo.season=:backSeason ");
				cond.append(" and planeInfo.day" + backDay + "='Y' ");
				cond.append(" ) or ( "); 
				turncityToDepart(cond, turncityAirport, departAirport);
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:backTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:backTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + backTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				
				cond.append(" ) ");
			}
		}
		HqlUtils.insertWhere(cond);
		logger.debug("cond: " + cond);
		
		Query query = session.createQuery("from PlaneInfo planeInfo " + cond + " order by planeInfo.departure.airportThreeCode asc, planeInfo.destination.airportThreeCode asc");
		if(company != null) {
			for(int i = 0; i < company.length; i ++) {
				query.setString("twocode" + i, company[i]);
			}
		}
		
		if(itineryType == PlaneInfo.TYPE_ONE_WAY) {
			if(turncityAirport == null || turncityAirport.length == 0) {
				for(int i = 0; i < departAirport.length; i ++) {
					query.setString("departAirport" + i, departAirport[i]);
				}
				for(int i = 0; i < destAirport.length; i ++) {
					query.setString("destAirport" + i, destAirport[i]);
				}
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
			} else {
				for(int i = 0; i < departAirport.length; i ++) {
					query.setString("departAirport" + i, departAirport[i]);
				}
				for(int i = 0; i < destAirport.length; i ++) {
					query.setString("destAirport" + i, destAirport[i]);
				}
				for(int i = 0; i < turncityAirport.length; i ++) {
					query.setString("turncityAirport" + i, turncityAirport[i]);
				}
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				
				for(int i = 0; i < toTransferYear.length; i ++) {
					query.setSerializable("toTransferYear" + i, toTransferYear[i]);
				}
				for(int i = 0; i < toTransferSeason.length; i ++) {
					query.setSerializable("toTransferSeason" + i, toTransferSeason[i]);
				}
			}
		} else {
			if(turncityAirport == null || turncityAirport.length == 0) {
				for(int i = 0; i < departAirport.length; i ++) {
					query.setString("departAirport" + i, departAirport[i]);
				}
				for(int i = 0; i < destAirport.length; i ++) {
					query.setString("destAirport" + i, destAirport[i]);
				}
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				query.setString("backYear", backYear);
				query.setString("backSeason", backSeason);
			} else {
				for(int i = 0; i < departAirport.length; i ++) {
					query.setString("departAirport" + i, departAirport[i]);
				}
				for(int i = 0; i < destAirport.length; i ++) {
					query.setString("destAirport" + i, destAirport[i]);
				}
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				query.setString("backYear", backYear);
				query.setString("backSeason", backSeason);
				
				for(int i = 0; i < turncityAirport.length; i ++) {
					query.setString("turncityAirport" + i, turncityAirport[i]);
				}
				for(int i = 0; i < toTransferYear.length; i ++) {
					query.setSerializable("toTransferYear" + i, toTransferYear[i]);
				}
				for(int i = 0; i < toTransferSeason.length; i ++) {
					query.setSerializable("toTransferSeason" + i, toTransferSeason[i]);
				}
				for(int i = 0; i < backTransferYear.length; i ++) {
					query.setSerializable("backTransferYear" + i, backTransferYear[i]);
				}
				for(int i = 0; i < backTransferSeason.length; i ++) {
					query.setSerializable("backTransferSeason" + i, backTransferSeason[i]);
				}
			}
		}
		
		return query.list();
	
	}
	
	private void departToDest(StringBuffer strbuf, String[] departAirport, String[] destAirport) {
		strbuf.append(" ( ");
		for(int i = 0; i < departAirport.length; i ++) {
			strbuf.append(" planeInfo.departure.airportThreeCode=:departAirport" + i);
			if(departAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) and ( ");
		for(int i = 0; i < destAirport.length; i ++) {
			strbuf.append(" planeInfo.destination.airportThreeCode=:destAirport" + i);
			if(destAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) ");
	}
	
	private void destToDepart(StringBuffer strbuf, String[] departAirport, String[] destAirport) {
		strbuf.append(" ( ");
		for(int i = 0; i < destAirport.length; i ++) {
			strbuf.append(" planeInfo.departure.airportThreeCode=:destAirport" + i);
			if(destAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) and ( ");
		for(int i = 0; i < departAirport.length; i ++) {
			strbuf.append(" planeInfo.destination.airportThreeCode=:departAirport" + i);
			if(departAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) ");
	}
	
	private void departToTurncity(StringBuffer strbuf, String[] departAirport, String[] turncityAirport) {
		strbuf.append(" ( ");
		for(int i = 0; i < departAirport.length; i ++) {
			strbuf.append(" planeInfo.departure.airportThreeCode=:departAirport" + i);
			if(departAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) and ( ");
		for(int i = 0; i < turncityAirport.length; i ++) {
			strbuf.append(" planeInfo.destination.airportThreeCode=:turncityAirport" + i);
			if(turncityAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) ");
	}
	
	private void turncityToDest(StringBuffer strbuf, String[] turncityAirport, String[] destAirport) {
		strbuf.append(" ( ");
		for(int i = 0; i < turncityAirport.length; i ++) {
			strbuf.append(" planeInfo.departure.airportThreeCode=:turncityAirport" + i);
			if(turncityAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) and ( ");
		for(int i = 0; i < destAirport.length; i ++) {
			strbuf.append(" planeInfo.destination.airportThreeCode=:destAirport" + i);
			if(destAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) ");
	}
	
	private void turncityToDepart(StringBuffer strbuf, String[] turncityAirport, String[] departAirport) {
		strbuf.append(" ( ");
		for(int i = 0; i < turncityAirport.length; i ++) {
			strbuf.append(" planeInfo.departure.airportThreeCode=:turncityAirport" + i);
			if(turncityAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) and ( ");
		for(int i = 0; i < departAirport.length; i ++) {
			strbuf.append(" planeInfo.destination.airportThreeCode=:departAirport" + i);
			if(departAirport.length -1 != i) {
				strbuf.append(" or ");
			}
		}
		strbuf.append(" ) ");
	}
	
	/**
	 * 从数据库中查询出航班信息
	 * @param departAirport 起飞机场三字吗
	 * @param destAirport 降落机场三字码
	 * @param year 起飞日期对应的年
	 * @param season 起飞日期对应的季度
	 * @param toDay 起飞日期对应的 星期几
	 * @param backDay 返回日期对应的 星期几
	 * @param offtime  起飞时间
	 * @param company  航空公司二字码，没有为 null
	 * @param turncity 转机地，没有为 null 
	 * @return ArrayList<Flight>
	 * @throws Exception
	 */
	public List getFlightList(
			String departAirport, String destAirport, String turncityAirport, 
			String toYear, String[] toTransferYear, 
			String toSeason, String[] toTransferSeason, 
			String toDay, String[] toTransferDay, 
			String backYear, String[] backTransferYear, 
			String backSeason, String[] backTransferSeason, 
			String backDay, String[] backTransferDay, 
			int itineryType, String[] company) throws Exception{

		
		
		Session session = getSession();
		
		StringBuffer cond = new StringBuffer();
		List<String> comConList = new ArrayList<String>();
		if(company != null) {
			for(int i = 0; i < company.length; i ++) {
				comConList.add(" planeInfo.carrier.twocode=:twocode" + i);
			}
		}
		
		if(!comConList.isEmpty()) {
			cond.append(" ( ");
			for(int i = 0; i < comConList.size(); i ++) {
				cond.append(comConList.get(i));
				if(i != comConList.size() - 1) cond.append(" or ");
			}
			cond.append(" ) ");
		}
		
		if(cond.length() > 0) {
			cond.append(" and ");
		}

		if(itineryType == PlaneInfo.TYPE_ONE_WAY) {
			if(turncityAirport == null) {
				// 单程且不中转
				cond.append(" planeInfo.departure.airportThreeCode=:departAirport and planeInfo.destination.airportThreeCode=:destAirport");
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
			} else {
				// 单程，有中转
				cond.append(" ( ");
				cond.append(" planeInfo.departure.airportThreeCode=:departAirport and planeInfo.destination.airportThreeCode=:turncityAirport ");
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( "); 
				cond.append(" planeInfo.departure.airportThreeCode=:turncityAirport and planeInfo.destination.airportThreeCode=:destAirport ");
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:toTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:toTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + toTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				cond.append(" ) "); 
			}
		} else {
			if(turncityAirport == null) {
				// 往返，无中转
				cond.append(" ( ");
				cond.append(" planeInfo.departure.airportThreeCode=:departAirport and planeInfo.destination.airportThreeCode=:destAirport");
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( ");
				cond.append(" planeInfo.departure.airportThreeCode=:destAirport and planeInfo.destination.airportThreeCode=:departAirport");
				cond.append(" and planeInfo.year=:backYear ");
				cond.append(" and planeInfo.season=:backSeason ");
				cond.append(" and planeInfo.day" + backDay + "='Y' ");
				cond.append(" ) ");
			} else {
				// 往返， 有中转
				cond.append(" ( ");
				cond.append(" planeInfo.departure.airportThreeCode=:departAirport and planeInfo.destination.airportThreeCode=:turncityAirport ");
				cond.append(" and planeInfo.year=:toYear ");
				cond.append(" and planeInfo.season=:toSeason ");
				cond.append(" and planeInfo.day" + toDay + "='Y' ");
				cond.append(" ) or ( "); 
				cond.append(" planeInfo.departure.airportThreeCode=:turncityAirport and planeInfo.destination.airportThreeCode=:destAirport ");
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:toTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:toTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + toTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				
				cond.append(" ) and (");
				
				cond.append(" planeInfo.departure.airportThreeCode=:turncityAirport and planeInfo.destination.airportThreeCode=:departAirport ");
				cond.append(" and planeInfo.year=:backYear ");
				cond.append(" and planeInfo.season=:backSeason ");
				cond.append(" and planeInfo.day" + backDay + "='Y' ");
				cond.append(" ) or ( "); 
				cond.append(" planeInfo.departure.airportThreeCode=:turncityAirport and planeInfo.destination.airportThreeCode=:departAirport ");
				cond.append(" and (");
				for(int i = 0; i < toTransferYear.length; i ++) {
					cond.append(" planeInfo.year=:backTransferYear" + i);
					if(toTransferYear.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and (");
				for(int i = 0; i < toTransferSeason.length; i ++) {
					cond.append(" planeInfo.season=:backTransferSeason" + i);
					if(toTransferSeason.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) and ( ");
				for(int i = 0; i < toTransferDay.length; i ++) {
					cond.append(" planeInfo.day" + backTransferDay[i] + "='Y' ");
					if(toTransferDay.length - 1 != i) {
						cond.append(" or ");
					}
				}
				cond.append(" ) ");
				
				cond.append(" ) ");
			}
		}
		HqlUtils.insertWhere(cond);
		logger.debug("cond: " + cond);
		
		Query query = session.createQuery("from PlaneInfo planeInfo " + cond);
		if(company != null) {
			for(int i = 0; i < company.length; i ++) {
				query.setString("twocode" + i, company[i]);
			}
		}
		
		if(itineryType == PlaneInfo.TYPE_ONE_WAY) {
			if(turncityAirport == null) {
				query.setString("departAirport", departAirport);
				query.setString("destAirport", destAirport);
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
			} else {
				query.setString("departAirport", departAirport);
				query.setString("destAirport", destAirport);
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				
				query.setString("turncityAirport", turncityAirport);
				for(int i = 0; i < toTransferYear.length; i ++) {
					query.setSerializable("toTransferYear" + i, toTransferYear[i]);
				}
				for(int i = 0; i < toTransferSeason.length; i ++) {
					query.setSerializable("toTransferSeason" + i, toTransferSeason[i]);
				}
			}
		} else {
			if(turncityAirport == null) {
				query.setString("departAirport", departAirport);
				query.setString("destAirport", destAirport);
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				query.setString("backYear", backYear);
				query.setString("backSeason", backSeason);
			} else {
				query.setString("departAirport", departAirport);
				query.setString("destAirport", destAirport);
				query.setString("toYear", toYear);
				query.setString("toSeason", toSeason);
				query.setString("backYear", backYear);
				query.setString("backSeason", backSeason);
				
				query.setString("turncityAirport", turncityAirport);
				for(int i = 0; i < toTransferYear.length; i ++) {
					query.setSerializable("toTransferYear" + i, toTransferYear[i]);
				}
				for(int i = 0; i < toTransferSeason.length; i ++) {
					query.setSerializable("toTransferSeason" + i, toTransferSeason[i]);
				}
				for(int i = 0; i < backTransferYear.length; i ++) {
					query.setSerializable("backTransferYear" + i, backTransferYear[i]);
				}
				for(int i = 0; i < backTransferSeason.length; i ++) {
					query.setSerializable("backTransferSeason" + i, backTransferSeason[i]);
				}
			}
		}
		
		return query.list();
	}
	
	public List getTransAirportOfCompany(String companyTwoCode) {
		return getHibernateTemplate().find("from CompanyTurncity companyTurncity where companyTurncity.comTwoCode=?", companyTwoCode);
	}
	
	public List getCompanyOfTransAirport(String airportThreeCode) {
		return getHibernateTemplate().find("from CompanyTurncity companyTurncity where companyTurncity.turnCityThreeCode.airportThreeCode=?", airportThreeCode);
	}
	
	public List getAllTurncity() {
		return getHibernateTemplate().find("from CompanyTurncity companyTurncity order by companyTurncity.turnCityThreeCode.airportThreeCode asc");
	}
	
	public List getDistinctTurncityThreecode() {
		return getHibernateTemplate().find("select distinct companyTurncity.turnCityThreeCode from CompanyTurncity companyTurncity order by companyTurncity.turnCityThreeCode.airportThreeCode asc");
	}
	
	public List getAirportByThreeCode(String threeCode) {
		return getHibernateTemplate().find("from ThreeCode threeCode where threeCode.airportThreeCode=?", threeCode);
	}
	
	public List queryThreecodeByStart(String str) {
		return getHibernateTemplate().find("from ThreeCode threeCode where threeCode.airportThreeCode like ? or threeCode.airportChineseName like ?", new String[]{str + "%", str + "%"});
	}
}
